﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

using Utils.Excel;

namespace Utils
{
    /// <summary>
    /// Excel操作类，支持IList,Linq的所有方法
    /// </summary>
    public static class ExcelNPOI
    {
        public static List<T> FromStream<T>(Stream stream, Func<IDictionary<string, string>, T> func = null, string sheetName = null)
        {
            if (stream == null) throw new ArgumentNullException("stream");
            return new ExcelNPOI<T>(stream, func, sheetName).ToList();
        }

        public static List<T> FromBytes<T>(byte[] bytes, Func<IDictionary<string, string>, T> func = null, string sheetName = null)
        {
            if (bytes == null) throw new ArgumentNullException("bytes");
            return FromStream(new MemoryStream(bytes), func, sheetName);
        }


        public static IExcel<T> CreateExcel<T>()
        {
            return new ExcelNPOI<T>();
        }

        public static IExcel<T> ToExcel<T>(this IEnumerable<T> source)
        {
            return new ExcelNPOI<T>().AddRange(source);
        }
    }
    /// <summary>
    /// 内部封装
    /// </summary>
    /// <typeparam name="T">实体</typeparam>
    sealed class ExcelNPOI<T> : ExcelBase<T>
    {
        public ExcelNPOI() : base()
        {

        }
        public ExcelNPOI(Stream stream, Func<IDictionary<string, string>, T> func, string sheetName) : base(stream, func, sheetName)
        {

        }

        public ExcelNPOI(Stream stream, Func<IDictionary<string, string>, int, T> func, string sheetName) : base(stream, func, sheetName)
        {

        }

        public void AddTitle(IWorkbook workbook, ISheet sheet, int cols, ref int startIndex)
        {
            if (string.IsNullOrEmpty(Title))
                return;
            IRow headerRow = sheet.CreateRow(startIndex++);
            var cell = headerRow.CreateCell(0);
            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.FillForegroundColor = HSSFColor.Orange.Index;
            headStyle.FillPattern = FillPattern.SolidForeground;
            headStyle.Alignment = HorizontalAlignment.Center;
            headerRow.HeightInPoints = 20;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;
            font.IsBold = true;
            font.Color = HSSFColor.Red.Index;
            headStyle.SetFont(font);
            cell.CellStyle = headStyle;
            cell.SetCellValue(Title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));
        }
        private void AddHeader(IWorkbook workbook, ISheet sheet, int index)
        {
            IRow headerRow = sheet.CreateRow(index);


            int i = 0;
            foreach (var item in columnProperty)
            {
                var cell = headerRow.CreateCell(i++);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.FillForegroundColor = HSSFColor.Orange.Index;
                headStyle.FillPattern = FillPattern.SolidForeground;
                headStyle.Alignment = HorizontalAlignment.Center;
                headerRow.HeightInPoints = 20;
                headStyle.VerticalAlignment = VerticalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 12;
                font.IsBold = true;
                if (item.Value.styles?.ContainsKey("required") ?? false)
                {
                    font.Color = HSSFColor.Red.Index;
                }
                headStyle.SetFont(font);
                cell.CellStyle = headStyle;
                cell.SetCellValue(item.Key);
            }

            sheet.CreateFreezePane(0, 1);
        }

        private static string GetNumericValue(ICell cell)
        {
            try
            {
                if (cell.CellStyle.DataFormat != 0)
                    return (cell.DateCellValue.ToString());
                return cell.NumericCellValue.ToString();
            }
            catch
            {
                return cell.ToString();
            }
        }
        private static string GetStringValue(ICell cell)
        {
            return cell.CellType switch
            {
                CellType.Numeric => GetNumericValue(cell),
                CellType.String => cell.ToString(),
                CellType.Boolean => cell.BooleanCellValue.ToString(),
                _ => cell.ToString(),
            };
        }

        private void ExcelRead(ISheet worksheet, int rowIndex = 0)
        {
            int rows = worksheet.LastRowNum;
            //获取worksheet的列数
            IRow headerRow = worksheet.GetRow(rowIndex);
            int cols = headerRow.LastCellNum;
            List<string> keys = new List<string>();

            for (int j = 0; j < cols; j++)
            {
                object cellName = headerRow.GetCell(j);
                if (cellName != null && !string.IsNullOrEmpty(cellName.ToString()))
                {
                    keys.Add(cellName.ToString());
                }
            }
            for (int i = rowIndex + 1; i <= rows; i++)
            {
                var dict = new Dictionary<string, string>();
                var row = worksheet.GetRow(i);
                if (row == null) continue;
                for (int j = 0; j < cols; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell != null)
                    {
                        dict.Add(keys[j], GetStringValue(cell));
                    }

                }
                if (dict.Values.All(a => string.IsNullOrWhiteSpace(a)))
                    break;
                Add(excelRead(dict, i));
            }
        }

        private static string CaseBoolean(bool value, string caseString)
        {

            var cases = caseString.Split(',');
            foreach (var c in cases)
            {
                var cs = c.Split(':');
                if (bool.Parse(cs[0]) == value) return cs[1];
            }
            return value ? "是" : "否";
        }

        private static string CaseDouble(double value, string caseString, string format)
        {
            var cases = caseString.Split(',');
            foreach (var c in cases)
            {
                var cs = c.Split(':');
                if (double.Parse(cs[0]) == value) return cs[1];
            }
            return value.ToString(format);
        }
        private static string CaseString(string value, string caseString)
        {

            var cases = caseString.Split(',');
            foreach (var c in cases)
            {
                var cs = c.Split(':');
                if (cs[0] == value) return cs[1];
            }
            return value;
        }

        private void TrySetValue(ICell cell, object value, Type propertyType, string format, string caseString)
        {
            if (value == null) return;

            if (typeof(IConvertible).IsInstanceOfType(value))
            {
                var convertible = (IConvertible)value;
                var typeCode = convertible.GetTypeCode();
                switch (typeCode)
                {
                    case TypeCode.Boolean:
                        if (!string.IsNullOrEmpty(caseString))
                        {
                            cell.SetCellValue(CaseBoolean(Convert.ToBoolean(value), caseString));
                        }
                        else
                        {
                            cell.SetCellValue(Convert.ToBoolean(value) ? "是" : "否");
                        }
                        return;
                    case TypeCode.DateTime:
                        if (format.IsNullOrEmpty())
                        {
                            cell.SetCellValue(Convert.ToDateTime(value).ToString(format));
                        }
                        else
                        {
                            cell.SetCellValue(Convert.ToDateTime(value));
                        }
                        return;
                    case TypeCode.Decimal:
                    case TypeCode.Double:
                    case TypeCode.Single:

                    case TypeCode.Int32:
                    case TypeCode.Int64:
                    case TypeCode.Int16:
                    case TypeCode.UInt32:
                    case TypeCode.UInt64:
                    case TypeCode.UInt16:
                        if (!string.IsNullOrEmpty(caseString))
                        {
                            cell.SetCellValue(CaseDouble(Convert.ToDouble(value), caseString, format));
                        }
                        else
                        {
                            if (format.IsNullOrEmpty())
                            {
                                cell.SetCellValue(Convert.ToDouble(value));
                            }
                            else
                            {
                                cell.SetCellValue(Convert.ToDouble(value).ToString(format));
                            }
                        }
                        return;
                    case TypeCode.DBNull:
                    case TypeCode.Empty:
                    case TypeCode.Object:
                        break;
                    default:
                        if (!string.IsNullOrEmpty(caseString))
                        {
                            cell.SetCellValue(CaseString(value.ToString(), caseString));
                        }
                        else
                        {
                            cell.SetCellValue(value.ToString());
                        }
                        return;
                }
            }
            throw new InvalidOperationException($"[不支持的类型:{propertyType.Name}]");
        }

        protected override void InitFromExcel(Stream stream, string sheetName)
        {
            IWorkbook workbook = WorkbookFactory.Create(stream);

            try
            {
                if (workbook.NumberOfSheets == 0)
                    throw Error.DefaultError("无效的格式");
                ISheet worksheet;
                if (string.IsNullOrEmpty(sheetName))
                {
                    worksheet = workbook.GetSheetAt(0);
                }
                else
                {
                    worksheet = workbook.GetSheet(sheetName);
                }
                if (worksheet == null)
                    throw Error.DefaultError("工作表" + sheetName + "不存在");

                if (excelRead == null)
                {
                    excelRead = ToEntity();
                }
                IRow headerRow = worksheet.GetRow(0);
                ExcelRead(worksheet, headerRow.GetCell(0).IsMergedCell ? 1 : 0);
            }
            finally
            {
                workbook.Close();

                stream.Dispose();
            }
        }

        public override byte[] ToBytes()
        {
            var workbook = new XSSFWorkbook();
            byte[] bytes = null;
            try
            {
                var sheet1 = workbook.CreateSheet("sheet1");

                int startIndex = 0;
                int c = 0;
                List<ICellStyle> cellStyles = new List<ICellStyle>();
                PropertyInfo mergeKey = null;
                foreach (var item in columnProperty)
                {
                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    cellStyle.Alignment = HorizontalAlignment.Center;

                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    var deffont = workbook.GetFontAt(0);
                    deffont.FontHeightInPoints = 11;
                    deffont.FontName = "宋体";
                    cellStyle.SetFont(deffont);

                    //     sheet1.AutoSizeColumn(c);
                    if (item.Value.styles != null)
                    {
                        foreach (var style in item.Value.styles)
                        {
                            switch (style.Key.ToLower())
                            {
                                case "width":
                                    sheet1.SetColumnWidth(c, (int)(style.Value.ToInt32() * 270));
                                    break;
                                case "text-align":
                                    switch (style.Value)
                                    {
                                        case "left":
                                            cellStyle.Alignment = HorizontalAlignment.Left;
                                            break;
                                        case "right":
                                            cellStyle.Alignment = HorizontalAlignment.Right;
                                            break;
                                        default:
                                            break;
                                    }
                                    break;
                                case "data-format":
                                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(style.Value);
                                    break;
                                case "merge":
                                    if (style.Value == "main")
                                    {
                                        // 合并主键
                                        mergeKey = item.Value.propertyInfo;
                                    }
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                    cellStyles.Add(cellStyle);
                    sheet1.SetDefaultColumnStyle(c, cellStyle);
                    c++;

                }

                AddTitle(workbook, sheet1, cellStyles.Count, ref startIndex);

                AddHeader(workbook, sheet1, startIndex++);

                object nextMianValue = null;
                int mergeStartIndex = 0;
                int mergeEndIndex = 0;
                for (int i = 0; i < current.Count; i++)
                {
                    int rowIndex = i + startIndex;
                    int j = 0;
                    var row = sheet1.CreateRow(rowIndex);
                    foreach (var item in columnProperty)
                    {

                        var cell = row.CreateCell(j);
                        cell.CellStyle = cellStyles[j];
                        string caseString = item.Value.styles?.TryGetValue("case");
                        var value = item.Value.propertyInfo.GetValue(current[i]);
                        if (mergeKey != null && item.Value.styles.ContainsKey("merge"))
                        { // 合并相同项

                            if (item.Value.styles["merge"] == "main")
                            {
                                if (nextMianValue == null || !nextMianValue.Equals(value))
                                {
                                    mergeStartIndex = rowIndex;
                                    nextMianValue = value;
                                }
                                if (i + 1 >= current.Count || !item.Value.propertyInfo.GetValue(current[i + 1]).Equals(value))
                                {
                                    mergeEndIndex = rowIndex;
                                }
                            }
                            if (mergeStartIndex == rowIndex)
                            {
                                TrySetValue(cell, value, item.Value.propertyInfo.PropertyType, item.Value.styles?.TryGetValue("format"), caseString);
                            }
                            else if (mergeEndIndex > mergeStartIndex)
                            {
                                sheet1.AddMergedRegion(new CellRangeAddress(mergeStartIndex, mergeEndIndex, j, j));
                            }
                        }
                        else
                        {
                            TrySetValue(cell, value, item.Value.propertyInfo.PropertyType, item.Value.styles?.TryGetValue("format"), caseString);
                        }

                        j++;
                    }
                }
                bytes = GetStream(workbook).ToArray();
            }
            catch
            {
                throw;
            }
            finally
            {
                workbook.Close();
            }
            return bytes;
        }

        private MemoryStream GetStream(IWorkbook workbook)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                return ms;
            }
        }
    }
}
